<?php 
	require_once("connector.php");
	require_once("baseobj.php");

	define("ADMIN_PROJECT_STATUS", "1");	
	define("MGR_TEAM_STATUS", "2");	
	define("USR_WKLY_TTLS", "3");	
	define("MONTLY_USER_TOTALS", "4");	
	define("USR_STATUS", "5");	
	define("MGR_MNTLY_STARS", "6");	
	define("ADMIN_LAZY_USERS", "7");	
	define("MGR_SLCT_USR_TTLS", "8");	
	define("ADMIN_VCNT_PJTS", "9");	
	
	class Report extends BaseObj {
		public $ReportId = 0;
		public $RoleId = 0;
		public $Name = "";
		public $Description = "";
		public $Value = "";
		
		
		public static function getForReportsForRole($roleid){ 			
			$arr = array();
			$i = 0;
			$sql = "SELECT r.* 
					FROM Reports r
					WHERE r.RoleId = :roleid";
					
			$conn = createConn();
			$stmt = oci_parse($conn, $sql);
			oci_bind_by_name($stmt, ':roleid', $roleid);
			$cnt = oci_execute($stmt);
			$r = null;
			if($cnt > 0) {				
				while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
					$r = new Report();
					$r->load($row);
					$arr[$i] = $r;
					$i++;
				}
			}			
			oci_free_statement($stmt);
			oci_close($conn);
			
			return $arr;
		}
		
		public function load($row){		
			$this->ReportId = $row["REPORTID"];
			$this->RoleId = $row["ROLEID"];
			$this->Name = $row["NAME"];
			$this->Description = $row["DESCRIPTION"];
		}
		public function userMonthlyTotals($userid, $month){		
			$arr = array();
			$i = 0;
			$sql = "SELECT SUM(tt.hours) AS Total, 
					to_char(to_date(trunc(tt.created), 'DD-MON-RR'), 'MONTH') As Month
				FROM tasktime tt 
				WHERE tt.userid = :userid and (extract(month from tt.created) = :month 
				or extract(month from tt.modified) = :month)
				group by tt.userid, to_char(to_date(trunc(tt.created), 'DD-MON-RR'), 'MONTH')";

			$conn = createConn();
			$stmt = oci_parse($conn, $sql);
			oci_bind_by_name($stmt, ':userid', $userid);
			oci_bind_by_name($stmt, ':month', $month);

			$cnt = oci_execute($stmt);
			$r = null;
			if($cnt > 0) {
				while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
					$r = new Report();
					$r->Name = $row["MONTH"];
					$r->Description = "Total Hours";
					$r->Value = $row["TOTAL"];
					$arr[$i] = $r;
					$i++;
				}
			}			
			oci_free_statement($stmt);
			oci_close($conn);
	
			return $arr;
		}
		public function selectUserMonthlyTotals($email){		
			$email ="%".$email."%";
			$arr = array();
			$i = 0;
			$sql = "Select rpt.Employee , ROUND(AVG(rpt.total))AS average
				from (select u.email AS Employee, SUM(tt.hours) AS total,
				to_char(to_date(trunc(tt.created), 'DD-MON-RR'), 'MONTH') AS month from tasktime tt, users u
				where tt.userid = u.userid and u.email like :email
				group by u.email, to_char(to_date(trunc(tt.created), 'DD-MON-RR'), 'MONTH')
				order by Employee, month) rpt
				group by rpt.Employee order by rpt.Employee asc";
			
			$conn = createConn();
			$stmt = oci_parse($conn, $sql);
			oci_bind_by_name($stmt, ':email', $email);

			$cnt = oci_execute($stmt);
			$r = null;
			if($cnt > 0) {
				while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
					$r = new Report();
					$r->Name = $row["EMPLOYEE"];
					$r->Description = "Average Monthly Hours";
					$r->Value = $row["AVERAGE"];
					$arr[$i] = $r;
					$i++;
				}
			}			
			oci_free_statement($stmt);
			oci_close($conn);
	
			return $arr;
		}


		public static function generate($reportid, $userid){
			switch($reportid)
			{
				case ADMIN_PROJECT_STATUS:
				{
					$arr = array();
					$i = 0;
					$sql = "SELECT users.email,tot.UserId, tot.Percent from Users,
						(SELECT tt.UserID, ROUND(((SUM(tt.hours))/(SUM(t.estimatedhours))*100),0) AS Percent
						FROM TaskTime tt, Task t, Users u
						WHERE u.userid = tt.userid 
						group by tt.UserId)tot
						where tot.UserId = users.userid order by Users.email asc";

					$conn = createConn();
					$stmt = oci_parse($conn, $sql);

					$cnt = oci_execute($stmt);
					$r = null;
					if($cnt > 0) {
						while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
							$r = new Report();
							$r->Name = $row["EMAIL"];
							$r->Description = "Percentage";
							$r->Value = $row["PERCENT"]."%";
							$arr[$i] = $r;
							$i++;
						}
					}			
					oci_free_statement($stmt);
					oci_close($conn);
			
					return $arr;
				}
				break;
				case MGR_TEAM_STATUS:
				{
					$arr = array();
					$i = 0;
					$sql = "SELECT Users.email,tot.UserId, tot.Percent from Users,
						(SELECT tt.UserID, ROUND(((SUM(tt.hours))/(SUM(t.estimatedhours))*100),0) AS Percent
						FROM TaskTime tt, Task t, Users u
						WHERE u.userid = tt.userid 
						group by tt.UserId)tot
						where tot.UserId = users.userid order by Users.email asc";

					$conn = createConn();
					$stmt = oci_parse($conn, $sql);

					$cnt = oci_execute($stmt);
					$r = null;
					if($cnt > 0) {
						while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
							$r = new Report();
							$r->Name = $row["EMAIL"];
							$r->Description = "Percentage";
							$r->Value = $row["PERCENT"]."%";
							$arr[$i] = $r;
							$i++;
						}
					}			
					oci_free_statement($stmt);
					oci_close($conn);
			
					return $arr;
				}
				break;
				case USR_WKLY_TTLS:
				{
					$arr = array();
					$i = 0;
					$sql = "SELECT t.userId,SUM(t.hours) AS TOTAL,r.Name, r.Description
						 FROM Reports r, TaskTime t WHERE r.reportid = :reportid 
						 and t.UserId = :userid and (t.created > (sysdate - 7) or t.modified > (sysdate - 7))
						 group by t.userid, r.Name, r.Description";

					$conn = createConn();
					$stmt = oci_parse($conn, $sql);
					oci_bind_by_name($stmt, ':userid', $userid);
					oci_bind_by_name($stmt, ':reportid', $reportid);

					$cnt = oci_execute($stmt);
					$r = null;
					if($cnt > 0) {
						while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
							$r = new Report();
							$r->Name = $row["NAME"];
							$r->Description = $row["DESCRIPTION"];
							$r->Value = $row["TOTAL"];
							$arr[$i] = $r;
							$i++;
						}
					}			
					oci_free_statement($stmt);
					oci_close($conn);
			
					return $arr;
				}
				break;
				case MONTLY_USER_TOTALS:
				{
					$arr = array();
					$i = 0;
					$sql = "SELECT t.userId,SUM(t.hours) AS TOTAL,r.Name, r.Description
						 FROM Reports r, TaskTime t WHERE r.reportid = :reportid 
						 and t.UserId = :userid and t.created > (sysdate - 14)
						 group by t.userid, r.Name, r.Description";

					$conn = createConn();
					$stmt = oci_parse($conn, $sql);
					oci_bind_by_name($stmt, ':userid', $userid);
					oci_bind_by_name($stmt, ':reportid', $reportid);

					$cnt = oci_execute($stmt);
					$r = null;
					if($cnt > 0) {
						while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
							$r = new Report();
							$r->Name = $row["NAME"];
							$r->Description = $row["DESCRIPTION"];
							$r->Value = $row["TOTAL"];
							$arr[$i] = $r;
							$i++;
						}
					}			
					oci_free_statement($stmt);
					oci_close($conn);
			
					return $arr;
				}
				break;
				case USR_STATUS:
				{
					$arr = array();
					$i = 0;
					$sql = "SELECT t.Name AS TaskName,ROUND(((SUM(tt.hours)/t.EstimatedHours) * 100),0) AS Percent,
						r.Name, r.Description
						FROM Task t, TaskTime tt, Reports r  
						WHERE t.taskid = tt.taskid and tt.userid = t.assigneduserid and
						t.assigneduserid = :userid and r.reportid = :reportid 
						group by tt.hours, t.Name, t.EstimatedHours,r.Name, r.Description
						order by t.Name";

					$conn = createConn();
					$stmt = oci_parse($conn, $sql);
					oci_bind_by_name($stmt, ':userid', $userid);
					oci_bind_by_name($stmt, ':reportid', $reportid);

					$cnt = oci_execute($stmt);
					$r = null;
					if($cnt > 0) {
						while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
							$r = new Report();
							$r->Name = $row["TASKNAME"];
							$r->Description = $row["NAME"];
							$r->Value = $row["PERCENT"]."%";
							$arr[$i] = $r;
							$i++;
						}
					}			
					oci_free_statement($stmt);
					oci_close($conn);
			
					return $arr;
				}
				break;
				case MGR_MNTLY_STARS:
				{
					$arr = array();
					$i = 0;
					$sql = "select query1.* from (select u.email, SUM(tt.hours) AS total, extract(month from tt.created)
						AS mnumber from tasktime tt, users u where u.userid = tt.userid
						group by u.email, extract(month from tt.created))query1,
						(select tot.mnumber, max(tot.total) as mvalue from 
							(select u.email, SUM(tt.hours) AS total, extract(month from tt.created)as mnumber from tasktime tt,
								users u where u.userid = tt.userid group by u.email, extract(month from tt.created)
								order by mnumber)tot group by tot.mnumber order by mnumber)max_results
						where query1.total = max_results.mvalue
						order by max_results.mnumber asc";

					$conn = createConn();
					$stmt = oci_parse($conn, $sql);

					$cnt = oci_execute($stmt);
					$r = null;
					if($cnt > 0) {
						while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
							$r = new Report();
							$r->Name = $row["EMAIL"];
							//hideous switch to compensate for the fact that I couldn't 
							// get the month formatted to a string AND order correctly
							// in the above query.  
							switch($row["MNUMBER"])
							{
								case 1:
								$r->Description = "January";
								break;
								case 2:
								$r->Description = "February";
								break;
								case 3:
								$r->Description = "March";
								break;
								case 4:
								$r->Description = "April";
								break;
								case 5:
								$r->Description = "May";
								break;
								case 6:
								$r->Description = "June";
								break;
								case 7:
								$r->Description = "July";
								break;
								case 8:
								$r->Description = "August";
								break;
								case 9:
								$r->Description = "September";
								break;
								case 10:
								$r->Description = "October";
								break;
								case 11:
								$r->Description = "November";
								break;
								case 12:
								$r->Description = "December";
								break;
				
							}
							$r->Value = $row["TOTAL"];
							$arr[$i] = $r;
							$i++;
						}
					}			
					oci_free_statement($stmt);
					oci_close($conn);
			
					return $arr;
				}
				break;
				case ADMIN_LAZY_USERS:
				{
					$arr = array();
					$i = 0;
					$sql = "select u.email from users u where u.userid not in(select tt.userid from TaskTime tt)
						order by u.email";

					$conn = createConn();
					$stmt = oci_parse($conn, $sql);

					$cnt = oci_execute($stmt);
					$r = null;
					if($cnt > 0) {
						while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
							$r = new Report();
							$r->Name = $row["EMAIL"];
							$r->Description = "No Reported Hours";
							$r->Value = 0;
							$arr[$i] = $r;
							$i++;
						}
					}			
					oci_free_statement($stmt);
					oci_close($conn);
			
					return $arr;
				}
				break;
				case ADMIN_VCNT_PJTS:
				{
					$arr = array();
					$i = 0;
					$sql = "SELECT p.Name from Project p
						where p.projectid not in 
						(select t.projectid from task t)
						order by p.Name asc";

					$conn = createConn();
					$stmt = oci_parse($conn, $sql);

					$cnt = oci_execute($stmt);
					$r = null;
					if($cnt > 0) {
						while($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
							$r = new Report();
							$r->Name = $row["NAME"];
							$r->Description = "No Associated Tasks";
							$arr[$i] = $r;
							$i++;
						}
					}			
					oci_free_statement($stmt);
					oci_close($conn);
			
					return $arr;
				}
				break;
			}

		}
		
	}
?>
